package com.wisemoresystem.seal.service;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.ne.framework.data.page.Page;
import org.ne.framework.data.task.Task;
import org.ne.framework.data.util.ChkTools;
import org.springframework.stereotype.Service;

import com.wisemoresystem.seal.base.service.BaseService;
import com.wisemoresystem.seal.model.t_seal_use_apply;
import com.wisemoresystem.seal.util.InTools;
import com.wisemoresystem.seal.util.SpringTools;
import com.wisemoresystem.seal.web.WebTools;

@Service
public class UseApplyService extends BaseService {

	@Task(code = "sdfsdfsdfkljl", tip = "待发送申请(@cnt)")
	public long selectToSendUseApplyList() {

		String sql = "select count(*) cnt from t_seal_use_apply t "
				+ " where t.status=0 and t.bank_id=? and t.pass_status='草稿'";
		Long cnt = super.getTaskCnt(sql, WebTools.getCurrentBankId());

		return cnt;
	}

	public List<Map<String, Object>> selectToSendUseApplyList(
			String srh_reason, String srh_user, Page page) {

		String sql = "select t.*,tu.uname from t_seal_use_apply t "
				+ " left join t_user tu on t.apply_user=tu._id "
				+ " where t.status=0 and t.bank_id=? and t.pass_status='草稿' and tu.uname like ? "
				+ " and t.reason like ? order by t.created desc ";

		List<String> params = new ArrayList<String>();

		params.add(WebTools.getCurrentBankId());
		if (ChkTools.isNull(srh_user)) {
			sql = sql.replace("and tu.uname like ?", "");
		} else {
			params.add("%" + srh_user + "%");
		}
		if (ChkTools.isNull(srh_reason)) {
			sql = sql.replace("and t.reason like ?", "");
		} else {
			params.add("%" + srh_reason + "%");
		}

		List<Map<String, Object>> list = jc.queryForList(sql, params.toArray(),
				page);
		// 获取使用申请中的附件
		for (Map<String, Object> map : list) {
			List<Map<String, Object>> files = getFiles(map);
			map.put("files", files);
		}
		return list;
	}

	public List<Map<String, Object>> selectUseApplyList(String srh_reason,
			String srh_user, Page page) {

		String sql = "select t.*,tu.uname from t_seal_use_apply t "
				+ " left join t_user tu on t.apply_user=tu._id "
				+ " where t.status=0 and t.bank_id=? and tu.uname like ? "
				+ " and t.reason like ? order by t.created desc ";

		List<String> params = new ArrayList<String>();

		params.add(WebTools.getCurrentBankId());
		if (ChkTools.isNull(srh_user)) {
			sql = sql.replace("and tu.uname like ?", "");
		} else {
			params.add("%" + srh_user + "%");
		}
		if (ChkTools.isNull(srh_reason)) {
			sql = sql.replace("and t.reason like ?", "");
		} else {
			params.add("%" + srh_reason + "%");
		}

		List<Map<String, Object>> list = jc.queryForList(sql, params.toArray(),
				page);
		// 获取使用申请中的附件
		for (Map<String, Object> map : list) {
			List<Map<String, Object>> files = getFiles(map);
			map.put("files", files);
		}
		return list;
	}

	// 需要盖章的apply list json
	public List<Map<String, Object>> selectUseApplyList() {

		String sql = "select t.*,tu.uname,count(tpp._id) cnt from t_seal_use_apply t "
				+ " left outer join t_user tu on t.apply_user=tu._id "
				+ " left outer join t_seal_use_apply_pp tpp on tpp.apply_id=t._id "
				+ " where t.status=0 and t.bank_id=? and t.pass_status='审批通过' "
				+ " and t.seal_status='待盖章' "
				+ " group by t._id,tu.uname "
				+ " order by t.created desc ";

		System.err.println(sql);
		List<String> params = new ArrayList<String>();

		params.add(WebTools.getCurrentBankId());

		List<Map<String, Object>> list = jt.queryForList(sql, params.toArray());

		return list;
	}

	// 获取使用申请中的附件
	private List<Map<String, Object>> getFiles(Map<String, Object> map) {
		String apply_file_ids = map.get("apply_file_ids") + "";
		List<Map<String, Object>> apply_files = getFile(apply_file_ids);

		String check_file_ids = map.get("check_file_ids") + "";
		List<Map<String, Object>> check_files = getFile(check_file_ids);

		String law_file_ids = map.get("law_file_ids") + "";
		List<Map<String, Object>> law_files = getFile(law_file_ids);

		String self_file_ids = map.get("self_file_ids") + "";
		List<Map<String, Object>> self_files = getFile(self_file_ids);

		String other_file_ids = map.get("other_file_ids") + "";
		List<Map<String, Object>> other_files = getFile(other_file_ids);

		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		list.addAll(apply_files);
		list.addAll(check_files);
		list.addAll(law_files);
		list.addAll(self_files);
		list.addAll(other_files);
		return list;
	}

	// 根据file_ids找到对应的文件
	private List<Map<String, Object>> getFile(String ids) {

		String file_ids = InTools.stringToSqlString(ids);
		String sql = "select * from t_file t where t.status=0 and t._id in (@ids)";
		sql = sql.replace("@ids", file_ids);
		List<Map<String, Object>> list = jt.queryForList(sql);
		return list;
	}

	public Map<String, Object> getMapFiles(t_seal_use_apply apply) {
		String apply_file_ids = apply.getApply_file_ids();
		List<Map<String, Object>> apply_files = getFile(apply_file_ids);

		String check_file_ids = apply.getCheck_file_ids();
		List<Map<String, Object>> check_files = getFile(check_file_ids);

		String law_file_ids = apply.getLaw_file_ids();
		List<Map<String, Object>> law_files = getFile(law_file_ids);

		String self_file_ids = apply.getSelf_file_ids();
		List<Map<String, Object>> self_files = getFile(self_file_ids);

		String other_file_ids = apply.getOther_file_ids();
		List<Map<String, Object>> other_files = getFile(other_file_ids);

		Map<String, Object> map = new HashMap<String, Object>();
		map.put("apply_files", apply_files);
		map.put("check_files", check_files);
		map.put("law_files", law_files);
		map.put("self_files", self_files);
		map.put("other_files", other_files);

		return map;
	}

	public List<Map<String, Object>> selectSeal(String bank_id) {
		String sql = "select t.* from t_seal t where t.status=0 and t.bank_id=? "
				+ " and t.flag_stop='暂时停用' and t.flag='停用' ";

		List<Map<String, Object>> list = jt.queryForList(sql, bank_id);
		return list;
	}

	@Task(code = "310", tip = "待审批列表(@cnt)")
	public long selectUnVerifyApplyList() {
		String sql = "select count(*) cnt from t_seal_use_apply t "
				+ " left join t_seal_use_apply_verify tvy on tvy.apply_id=t._id "
				+ " where t.status=0 and tvy.verify='待审批' and tvy.job_code=? and t.bank_id=? ";

		Long cnt = super.getTaskCnt(sql, WebTools.getCurrentUser()
				.getJob_code(), WebTools.getCurrentBankId());

		return cnt;
	}

	public List<Map<String, Object>> selectUnVerifyApplyList(Page page) {
		// TODO 查询 wkflow 待审批 的列表
		String sql = "select t.*,tu.uname,tvy._id verify_id from t_seal_use_apply t "
				+ " left join t_user tu on t.apply_user=tu._id "
				+ " left join t_seal_use_apply_verify tvy on tvy.apply_id=t._id "
				+ " where t.status=0 and tvy.verify='待审批' and tvy.job_code=? and t.bank_id=? "
				+ " order by t.created desc ";

		List<String> params = new ArrayList<String>();
		params.add(WebTools.getCurrentUser().getJob_code());
		params.add(WebTools.getCurrentBankId());

		List<Map<String, Object>> list = jc.queryForList(sql, params.toArray(),
				page);
		return list;
	}

	public List<Map<String, Object>> selectVerifyList(String apply_id) {
		// TODO 查询 wkflow 所有列表
		String sql = "select t.*,tbj.name job_name,tu.uname from t_seal_use_apply_verify t "
				+ " left outer join t_bank_job tbj on tbj.code=t.job_code "
				+ " left outer join t_user tu on tu._id=t.verify_user_id "
				+ " where t.status=0 and t.apply_id=? "
				+ " order by t.vorder asc";
		List<Map<String, Object>> list = jt.queryForList(sql, apply_id);

		return list;
	}

	public int selectMaxPPOrder(String apply_id) {
		String sql = "select max(t.vorder) vorder from t_seal_use_apply_pp t where t.apply_id=?";
		Integer vorder = (Integer) jt.queryForMap(sql, apply_id).get("vorder");
		if (vorder == null) {
			vorder = -1;
		}
		return vorder;
	}

	public static void main(String[] args) {
		UseApplyService s = SpringTools.getBean(UseApplyService.class);
		System.err.println(s.selectMaxPPOrder("xx"));

		System.err.println(s.selectUseApplyList());
	}
}
